package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Repository;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.dao.ExperienceCollectionDao;
import com.qdlc.p2p.dal.dto.ExperienceCollection;
import com.qdlc.p2p.dal.dto.User;

/**
 * 体验金投标记录DAO接口
 * @author xxx
 * @version 2.0
 * @since 2015年04月27日 18时37分28秒
 */
@Repository(value = "experienceCollectionDao")
public class ExperienceCollectionDaoImpl extends BaseDaoImpl<ExperienceCollection> implements ExperienceCollectionDao {

    @SuppressWarnings("unchecked")
    @Override
    public ExperienceCollection getAdditionalExperienceCollectionById(long id) {
        String jpql = "from ExperienceCollection where id = ?1";
        Query query = em.createQuery(jpql);
        query.setParameter(1, id);
        List<ExperienceCollection> list = query.getResultList();
        if (list != null && list.size() >= 0) {
            return (ExperienceCollection) list.get(0);
        } else {
            return null;
        }
    }

	@Override
	public List<ExperienceCollection> find(long borrowId, byte period) {
		QueryParam param = QueryParam.getInstance();
		param.addParam("borrow.id", borrowId);
		param.addParam("period", period);
		param.addParam("status", 0);
		return findByCriteria(param);
	}

	@SuppressWarnings("rawtypes")
	@Override
	public double getExperienceCollection(String year, String month, byte status) {
		String sql = "SELECT SUM(interest) FROM " + Global.DB_PREFIX + "additional_experience_collection WHERE DATE_FORMAT(repayment_yes_time,'%Y') = '"+year+"' AND DATE_FORMAT(repayment_yes_time,'%m') = '"+month+"' AND status = " + status;
		Query query = em.createNativeQuery(sql);
		List list = query.getResultList();
		if (list != null && list.size() > 0) {
			return Double.parseDouble(list.get(0).toString());
		}
		return 0;
	}
	
	@Override
	public double sumCollectionExperienceCapital(long collectionId) {
		String sql = "SELECT SUM(capital) FROM " + Global.DB_PREFIX + "additional_experience_collection WHERE collection_id = ?1";
		Query query = em.createNativeQuery(sql).setParameter(1, collectionId);
		Object obj = query.getSingleResult();
		if (obj != null) {
			return Double.parseDouble(obj.toString());
		}
		return 0;
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List<Object[]> getCollectionExperienceCapital(long collectionId) {
		String sql = "SELECT IFNULL(capital, 0), IFNULL(interest, 0) FROM " + Global.DB_PREFIX + "additional_experience_collection WHERE collection_id = ?1";
		Query query = em.createNativeQuery(sql).setParameter(1, collectionId);
		List list = query.getResultList();
		List<Object[]> objList = new ArrayList<Object[]>();
		if (list != null && list.size() > 0) {
			for (int i = 0; i < list.size(); i++) {
				Object[] values = (Object[]) list.get(i);
				objList.add(values);
			}
		}
		return objList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getExperienceCollectionInterest(User user, String date) {
		String sql = "select DATE_FORMAT(repayment_time,'%m-%d'),sum(interest) from " + Global.DB_PREFIX + "additional_experience_collection where status = 0  and DATE_FORMAT(repayment_time,'%Y-%m') = :timeDate and user_id = :userId group by repayment_time ";
		Query q = em.createNativeQuery(sql).setParameter("timeDate", date).setParameter("userId", user.getUserId());
	    return q.getResultList();
	}
}
